import re
import unittest

from base_parser import JsonParser
from ddl_parser import parse_ddl, TableInfoMgr
from dml_parser import DMLParser, DMLMetaInfo


class SQLChecker(JsonParser):
    def __init__(self, tbl_info_mgr: TableInfoMgr):
        self.tbl_info_mgr = tbl_info_mgr

    def check(self, sql: str):
        err_list = []
        dml_parser = DMLParser()
        meta_infos = dml_parser.parse(sql)
        for meta_info in meta_infos:
            if meta_info.sql_type == DMLMetaInfo.SQLTYPE_TRUNC:
                err_list.append("truncate table may be dangerous!")
                continue

            if len(meta_info.get_table_alias_mapping()) >= 3:
                err_list.append(
                    "too many tables:%s to join, it may be slow"
                    % (list(meta_info.get_table_alias_mapping().values()))
                )

            self.check_no_where(err_list, meta_info)

            cols = self.find_like_leading_percent(meta_info.get_origin_sql())
            if cols:
                err_list.append(
                    "columns:%s has like query with leading %%, it cannot use index and maybe slow"
                    % cols
                )

            self.check_index(err_list, meta_info)
        return err_list

    PAT_LIKE_CONCAT1 = re.compile("([`\w\.]+)\s+like\s+concat\s*\(\s*'%'", re.I)
    PAT_LIKE_CONCAT2 = re.compile("([`\w\.]+)\s+like\s+'%", re.I)

    def find_like_leading_percent(self, sql: str):
        like_cols = []
        for pat in (self.PAT_LIKE_CONCAT1, self.PAT_LIKE_CONCAT2):
            like_cols.extend(pat.findall(sql))
        return like_cols

    def check_no_where(self, err_list, meta_info):
        if not self.has_where(meta_info.get_grammar_tree()) and not self.has_limit(
            meta_info.get_grammar_tree()
        ):
            if meta_info.sql_type == DMLMetaInfo.SQLTYPE_SEL:
                err_list.append("no where condition, it is a full-table scan")
            elif meta_info.sql_type != DMLMetaInfo.SQLTYPE_INS:
                err_list.append(
                    "no where condition, a full-table update or delete is dangerous!"
                )

    def has_where(self, grammar_json):
        where_parts = self.find_match_values(DMLParser.WHERE_FINDER, grammar_json)
        return where_parts and where_parts.count(None) != len(where_parts)

    def has_limit(self, grammar_json):
        limit_parts = self.find_match_values(DMLParser.LIMIT_FINDER, grammar_json)
        return limit_parts and limit_parts.count(None) != len(limit_parts)

    def check_index(self, err_list, meta_info):
        conds = meta_info.get_conditions()
        for cond in conds:
            tbl_info = self.tbl_info_mgr.get(cond.owner)
            if tbl_info and not tbl_info.field_has_index(cond.fld):
                err_list.append(
                    "condition field:%s not in table:%s's index"
                    % (cond.fld, cond.owner)
                )


class SQLCheckerTest(unittest.TestCase):
    def test_check_index(self):
        checker = SQLChecker(
            parse_ddl(
                """
        create table T1 (id int, status int, time int, primary key(id), key(TIME));
        """
            )
        )
        err_list = checker.check(
            """
        delete from t1 where status = 0 and time > 10
        """
        )
        self.assertEqual(len(err_list), 1)
        self.assertEqual(err_list[0], "condition field:status not in table:t1's index")

    def test_check_full_table_scan(self):
        checker = SQLChecker(TableInfoMgr())
        err_list = checker.check(
            """
        select * from t1
        """
        )
        self.assertEqual(len(err_list), 1)
        self.assertEqual(err_list[0], "no where condition, it is a full-table scan")

    def test_check_limit(self):
        checker = SQLChecker(TableInfoMgr())
        err_list = checker.check(
            """
        select * from t1 limit ?;
        select * from t2 limit 10;
        """
        )
        self.assertEqual(len(err_list), 0)

    def test_check_full_table_upt(self):
        checker = SQLChecker(TableInfoMgr())
        err_list = checker.check(
            """
        update t1 set col1='a'
        """
        )
        self.assertEqual(len(err_list), 1)
        self.assertEqual(
            err_list[0],
            "no where condition, a full-table update or delete is dangerous!",
        )

    def test_check_truncate(self):
        checker = SQLChecker(TableInfoMgr())
        err_list = checker.check(
            """
        truncate table t1;
        """
        )
        self.assertEqual(len(err_list), 1)
        self.assertEqual(
            err_list[0],
            "truncate table may be dangerous!",
        )

    def test_check_too_many_tbls(self):
        checker = SQLChecker(TableInfoMgr())
        err_list = checker.check(
            """
        select * from t1 join t2 on t1.id=t2.id left join t3 on t1.id=t3.id where t1.id < 100
        """
        )
        self.assertEqual(len(err_list), 1)
        self.assertEqual(
            err_list[0],
            "too many tables:['t1', 't2', 't3'] to join, it may be slow",
        )

    def test_check_query_like(self):
        checker = SQLChecker(TableInfoMgr())
        err_list = checker.check(
            """
        select * from tbl t1 where col1 like concat('%', ?) or t1.col2 LIKE CONcat ('%',?,'%') or `col3` like '%abc%'
         or col4 like 'abc%' or col5 like concat(?, '%');
        """
        )
        print(err_list)
        self.assertEqual(len(err_list), 1)
        self.assertEqual(
            err_list[0],
            "columns:['col1', 't1.col2', '`col3`'] has like query with leading %, it cannot use index and maybe slow",
        )
